/* 
 * Copyright 2011 kOSMik.
 * This file is part of kOSMik.
 * 
 * kOSMik is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * kOSMik is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with kOSMik.  If not, see <http://www.gnu.org/licenses/>.
 *  
 * kOSMik:
 * Open Street Map - Editor.
 * http://code.google.com/p/swp-dv-ws2010-osm-2/
 */

package de.fuberlin.kosmik.data;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import de.fuberlin.kosmik.data.dao.Poi;
import de.fuberlin.kosmik.data.dao.Point;
import de.fuberlin.kosmik.data.dao.TrackData;
import de.fuberlin.kosmik.data.dao.TrackRecord;
import de.fuberlin.kosmik.data.dao.Way;
import de.fuberlin.kosmik.log.Constants;

/**
 * Implementation of the DataBaseConnector.
 */
public class DataBaseConnectorImpl implements DataBaseConnector {
	// Instance of database helper
	private DataOpenHelper openHelper;
	private SQLiteDatabase readableDataBase;
	private SQLiteDatabase writableDataBase;
	private static final String TAG = Constants.DB_HELPER;

	/**
	 * Constructor.
	 *
	 * @param context the Context of the caller
	 */
	public DataBaseConnectorImpl(Context context) {
		openHelper = new DataOpenHelper(context);
		readableDataBase = openHelper.getReadableDatabase();
		writableDataBase = openHelper.getWritableDatabase();
	}

	@Override
	public long addTrack(String name, long timestamp) {
		try {
			writableDataBase.beginTransaction();
			ContentValues values = new ContentValues();
			values.put(DBNames.Track.NAME, name);
			values.put(DBNames.Track.TIME, Long.valueOf(timestamp));
			long result = writableDataBase.insertOrThrow(DBNames.TRACK, null, values);
			if (result < 0)
				throw new SQLException("Inserting track failed, resulting: " + result);
			writableDataBase.setTransactionSuccessful();
			return result;
		} finally {
			writableDataBase.endTransaction();
		}
	}

	@Override
	public long addPoint(double longitude, double latitude, long timestamp, long trackId) {
		try {
			writableDataBase.beginTransaction();
			ContentValues values = new ContentValues();
			values.put(DBNames.Point.LNG, new Double(longitude));
			values.put(DBNames.Point.LAT, new Double(latitude));
			values.put(DBNames.Point.TIME, Long.valueOf(timestamp));
			values.put(DBNames.Point.TRACK_ID, Long.valueOf(trackId));
			long result = writableDataBase.insertOrThrow(DBNames.POINT, null, values);
			if (result < 0)
				throw new SQLException("Inserting point failed");
			writableDataBase.setTransactionSuccessful();
			return result;
		} finally {
			writableDataBase.endTransaction();
		}

	}

	@Override
	public void addPoi(long pointId, Bundle tags) {

		try {
			writableDataBase.beginTransaction();
			ContentValues values = new ContentValues();
			values.put(DBNames.Poi.POINT_ID, Long.valueOf(pointId));
			long poiId = writableDataBase.insertOrThrow(DBNames.POI, null, values);
			if (poiId < 0) {
				Log.w(TAG, "Inserting POI failed with poiId " + poiId);
				return;
			}
			if (null == tags) {
				return;
			}
			Set<String> set = tags.keySet();
			for (String key : set) {
				String value = tags.getString(key);
				if (key != null && value != null) {
					values = new ContentValues();
					values.put(DBNames.PoiValue.K, key);
					values.put(DBNames.PoiValue.V, value);
					values.put(DBNames.PoiValue.POI_ID, Long.valueOf(poiId));
					if (writableDataBase.insertOrThrow(DBNames.POI_VALUE, null, values) < 0) {
						Log.w(TAG, "Inserting POI TAG failed");
					}
				}
			}
			writableDataBase.setTransactionSuccessful();
		} finally {
			writableDataBase.endTransaction();
		}

	}

	@Override
	public long startWay(Bundle tags, boolean isArea) {

		try {
			writableDataBase.beginTransaction();

			ContentValues values = new ContentValues();
			values.put(DBNames.Way.IS_AREA, Boolean.valueOf(isArea));
			long wayId = writableDataBase.insert(DBNames.WAY, null, values);

			if (wayId <= 0) {
				Log.w(TAG, "saving way returned a non-positive id: " + wayId);
			} else {
				Log.d(TAG, "starting a way " + wayId + (isArea ? "which is an area" : ""));
			}
			if (tags == null) {
				Log.w(TAG, "Illegal arguments: tags must not be null");
				return -1;
			}

			for (String key : tags.keySet()) {

				values = new ContentValues();

				values.put(DBNames.WayValue.K, key);
				values.put(DBNames.WayValue.V, tags.getString(key));
				values.put(DBNames.WayValue.WAY_ID, Long.valueOf(wayId));

				if (writableDataBase.insert(DBNames.WAY_VALUE, null, values) < 0) {
					throw new SQLException("Inserting way TAG failed");
				}
			}

			Log.d(TAG, "started area " + wayId + " with values: " + values);

			writableDataBase.setTransactionSuccessful();
			return wayId;

		} finally {

			writableDataBase.endTransaction();

		}

	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void addPointToWay(long pointId, long wayId) {

		Log.d(Constants.TAG, "called db.addPointToWay poID:" + pointId + " / " + wayId);
		try {
			writableDataBase.beginTransaction();

			ContentValues values = new ContentValues();

			values.put(DBNames.PointsHaveWays.POINT_ID, Long.valueOf(pointId));
			values.put(DBNames.PointsHaveWays.WAY_ID, Long.valueOf(wayId));

			long insertValue = writableDataBase.insert(DBNames.POINTS_HAVE_WAYS, null, values);

			if (insertValue < 0) {
				Log.w(TAG, "Adding point to way failed with value:" + insertValue);
			}

			writableDataBase.setTransactionSuccessful();

		} catch (SQLException e) {
			Log.e(TAG, "Adding point to way failed raised exception");
			e.printStackTrace();
		} finally {
			writableDataBase.endTransaction();
		}

	}

	/**
	 * UNDER DEVELOPMENT: gives back all the points in the data base Find all
	 * points of a certain way.
	 *
	 * @param wayId - the data base ID of the way wanted
	 * @return - a list with lng's in even positions, starting with position
	 *         zero, and the matching lat's in non-even position, starting with
	 *         position one.
	 */
	@Override
	public List<Point> getPointsFromWay(long wayId) {
		Cursor cursor = null;
		ArrayList<Point> points = new ArrayList<Point>();

		try {
			String sql = String
					.format("select pId, point.lat as lat, point.lng as lng, point.time as time from (select point_id as pId from points_have_ways where points_have_ways.way_id = %d) join point on (pId = point.id);",
							wayId);

			cursor = readableDataBase.rawQuery(sql, null);

			int ipId = cursor.getColumnIndex("pId");
			int ilat = cursor.getColumnIndex("lat");
			int ilng = cursor.getColumnIndex("lng");
			int itime = cursor.getColumnIndex("time");

			boolean firstItem = true;

			while (cursor.moveToNext()) {

				if (firstItem)
					cursor.moveToFirst();
				firstItem = false;

				long pId = cursor.getLong(ipId);
				long time = cursor.getLong(itime);
				double lng = cursor.getDouble(ilng); // 180
				double lat = cursor.getDouble(ilat); // 90
				if (Math.abs(lng) <= 180L && Math.abs(lat) <= 90L) {
					points.add(new Point(pId, lng, lat, time));
				} else {
					Log.w(TAG, "found coordinates with wrong values for wayId:" + wayId
							+ "lat: " + lat + "lng: " + lng);
				}
			}
			return points;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
		}

	}

	@Override
	public Collection<Long> getWayIdsOfTrack(long trackId) {

		Collection<Long> wayIds = new ArrayList<Long>();

		String sql = String
				.format("select distinct points_have_ways.way_id as wayId from points_have_ways join (select point.id as pId from point where point.track_id = %d) on (pId = points_have_ways.point_id);",
						trackId);
		Cursor c = null;
		try {
			c = readableDataBase.rawQuery(sql, null);
			int iwayId = c.getColumnIndex("wayId");
			boolean isFirst = true;

			while (c.moveToNext()) {
				if (isFirst) {
					c.moveToFirst();
					isFirst = false;
				}
				long wayId = c.getLong(iwayId);
				wayIds.add(wayId);
			}

			return wayIds;

		} finally {
			if (c != null)
				c.close();
		}
	}

	/**
	 * UNDER DEVELOPMENT: gives back all the points in the data base Find all
	 * points of a certain track.
	 *
	 * @param trackId - the data base ID of the way wanted
	 * @return - a list with lng's in even positions, starting with position
	 *         zero, and the matching lat's in non-even position, starting with
	 *         position one.
	 */
	@Override
	public List<Double> getCoordinatesFromTrack(long trackId) {
		// TODO: select just the point of a way and not all points
		Cursor cursor = null;
		try {
			readableDataBase.beginTransaction();
			ArrayList<Double> points = new ArrayList<Double>();

			StringBuilder where = new StringBuilder();

			where.append(DBNames.Point.TRACK_ID);
			where.append("=");
			where.append(String.valueOf(trackId));

			cursor = query(DBNames.POINT, new String[]{"lng", "lat"}, where.toString(),
					"time asc");

			boolean firstItem = true;

			while (cursor.moveToNext()) {

				if (firstItem)
					cursor.moveToFirst();
				firstItem = false;

				double lng = cursor.getDouble(0); // 180
				double lat = cursor.getDouble(1); // 90
				if (Math.abs(lng) <= 180L && Math.abs(lat) <= 90L) {
					points.add(new Double(lng));
					assert points.size() % 2 == 0;
					points.add(new Double(lat));
					assert points.size() % 2 != 0;
				} else {
					throw new IllegalArgumentException(
							"found coordinates with wrong values for trackId:" + trackId
									+ "lat: " + lat + "lng: " + lng);
				}
			}
			readableDataBase.setTransactionSuccessful();
			return points;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
			readableDataBase.endTransaction();
		}

	}

	@Override
	public Collection<TrackRecord> getAllTracksInTimeWindow(long startTimeStamp,
															long endTimeStamp) {
		Cursor cursor = null;
		String order = DBNames.Track.TIME + " DESC";
		String sql = String.format(
				"select %s, %s, %s from %s where %s between %s and %s order by %s",
				DBNames.Track.ID, DBNames.Track.NAME, DBNames.Track.TIME, DBNames.TRACK,
				DBNames.Track.TIME, String.valueOf(startTimeStamp),
				String.valueOf(endTimeStamp), order);

		Collection<TrackRecord> result = new Vector<TrackRecord>();

		try {

			readableDataBase.beginTransaction();

			cursor = readableDataBase.rawQuery(sql, null);

			boolean firstOne = true;

			while (cursor.moveToNext()) {
				if (firstOne) {
					cursor.moveToFirst();
					firstOne = false;
				}
				result.add(new TrackRecord(cursor.getLong(0), cursor.getString(1), cursor
						.getLong(2)));
			}
			readableDataBase.setTransactionSuccessful();
		} finally {
			if (null != cursor) {
				cursor.close();
			}
			readableDataBase.endTransaction();
		}

		return result;

	}

	/**
	 * Will return a map from id to name of all tracks in the data base.
	 *
	 * @return - map from id to name
	 */
	@Override
	public Map<Long, String> getAllTracks() {
		Cursor cursor = null;
		try {

			readableDataBase.beginTransaction();

			cursor = query(DBNames.TRACK,
					new String[]{DBNames.Track.ID, DBNames.Track.NAME}, null, null);

			Map<Long, String> result = new TreeMap<Long, String>();

			boolean firstOne = true;

			while (cursor.moveToNext()) {
				if (firstOne) {
					cursor.moveToFirst();
					firstOne = false;
				}
				result.put(Long.valueOf(cursor.getLong(0)), cursor.getString(1));
			}

			readableDataBase.setTransactionSuccessful();
			return result;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
			readableDataBase.endTransaction();
		}

	}

	@Override
	public void addMedia(String format, String content, long pointId) {
		try {
			writableDataBase.beginTransaction();
			ContentValues values = new ContentValues();
			values.put(DBNames.Media.CONTENT, content);
			values.put(DBNames.Media.FORMAT, format);
			values.put(DBNames.Media.POINT_ID, Long.valueOf(pointId));
			long result = writableDataBase.insert(DBNames.MEDIA, null, values);
			if (result < 0) {
				Log.w(TAG, "Inserting media failed");
			}
			writableDataBase.setTransactionSuccessful();
		} finally {
			writableDataBase.endTransaction();
		}

	}

	/**
	 * Get read access to the databse.
	 *
	 * @return database instance with read access.
	 */
	public SQLiteDatabase getReadableDataBase() {
		Log.d(Constants.TAG, "trying to get a readable data base");
		return readableDataBase;
	}

	/**
	 * Get write access to the database.
	 *
	 * @return database instance with rw access.
	 */
	public SQLiteDatabase getWritableDataBase() {
		Log.d(Constants.TAG, "trying to get a readable data base");
		return writableDataBase;
	}

	/**
	 * Execute the given SQL statement in the db.
	 *
	 * @param sql statement to be executed.
	 */
	public void execSQL(String sql) {
		try {
			writableDataBase.beginTransaction();
			writableDataBase.execSQL(sql);
			writableDataBase.setTransactionSuccessful();
		} finally {
			writableDataBase.endTransaction();
		}
	}

	private Cursor query(String table, String[] columns, String selection, String orderBy) {
		Cursor cursor;
		cursor = readableDataBase.query(table, columns, selection, null, null, null, orderBy);
		readableDataBase.setTransactionSuccessful();
		return cursor;
	}

	/**
	 * Searching the data base for all POIs of a given track, create their DAO
	 * objects and gives them back.
	 *
	 * @param trackId - the track id of which the POIs are wanted
	 * @return - Collection of all Poi objects with their attributes as a bundle
	 */
	@Override
	public Collection<Poi> getPoisFromTrack(long trackId) {
		Cursor cursor = null;
		Collection<Poi> pois = new ArrayList<Poi>();

		readableDataBase.beginTransaction();

		try {
			String sql = String
					.format("select pId, pLng, pLat, pTime, poiId, poi_value.k as k, poi_value.v as v from (select pId, pLng, pLat, pTime, poi.id as poiId from (select point.id as pId, point.lng as pLng, point.lat as pLat, point.time as pTime from point where point.track_id = %s) join poi on (poi.point_id = pId)) left join poi_value on (poi_value.poi_id = poiId)",
							String.valueOf(trackId));

			cursor = readableDataBase.rawQuery(sql, null);

			int ipId = cursor.getColumnIndex("pId");
			int ipLng = cursor.getColumnIndex("pLng");
			int ipLat = cursor.getColumnIndex("pLat");
			int ipTime = cursor.getColumnIndex("pTime");
			int ipoiId = cursor.getColumnIndex("poiId");
			int ik = cursor.getColumnIndex("k");
			int iv = cursor.getColumnIndex("v");

			boolean first = true;
			Poi thisPoi = null;

			while (cursor.moveToNext()) {
				if (first) {
					cursor.moveToFirst();
					first = false;
				}

				long poiId = cursor.getLong(ipoiId);
				long pointId = cursor.getLong(ipId);
				String key = cursor.getString(ik);
				String value = cursor.getString(iv);
				double lng = cursor.getDouble(ipLng);
				double lat = cursor.getDouble(ipLat);
				long timestamp = cursor.getLong(ipTime);

				if (thisPoi == null || thisPoi.poiId != poiId) {
					thisPoi = new Poi(poiId, pointId, lng, lat, timestamp, new Bundle());
					pois.add(thisPoi);
				}

				thisPoi.putValue(key, value);

			}

			readableDataBase.setTransactionSuccessful();
			return pois;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
			readableDataBase.endTransaction();
		}

	}

	@Override
	public Collection<Point> getPointsFromTrack(long trackId) {
		Cursor cursor = null;
		try {
			String sql = String.format("select point.id as pId, point.lng as pLng, "
					+ "point.lat as pLat, point.time as pTime from"
					+ " point where point.track_id = %s;", String.valueOf(trackId));

			Collection<Point> points = new ArrayList<Point>();

			cursor = readableDataBase.rawQuery(sql, null);

			int ipId = cursor.getColumnIndex("pId");
			int ipLng = cursor.getColumnIndex("pLng");
			int ipLat = cursor.getColumnIndex("pLat");
			int ipTime = cursor.getColumnIndex("pTime");

			boolean first = true;

			while (cursor.moveToNext()) {
				if (first) {
					cursor.moveToFirst();
					first = false;
				}

				long pointId = cursor.getLong(ipId);
				double lng = cursor.getDouble(ipLng);
				double lat = cursor.getDouble(ipLat);
				long timestamp = cursor.getLong(ipTime);

				points.add(new Point(pointId, lng, lat, timestamp));
			}
			return points;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
		}
	}

	/**
	 * Remove a poi from the data base.
	 *
	 * @param poiId - id of poi to be removed
	 */
	@Override
	public void removePoi(long poiId) {
		try {
			writableDataBase.beginTransaction();
			String sql = String
					.format("delete from poi where poi.id=%s", String.valueOf(poiId));
			writableDataBase.rawQuery(sql, null);
			writableDataBase.setTransactionSuccessful();
		} finally {
			writableDataBase.endTransaction();
		}
	}

	/**
	 * Removes a track, as well as all the tables related to it.
	 *
	 * @param trackId data base track id of the track to be removed
	 */
	@Override
	public boolean removeTrack(long trackId) {

		Map<String, Collection<Long>> ids = getPointWayPoiIdsFromTrack(trackId);

		StringBuilder pointIds = new StringBuilder();
		boolean first = true;
		for (Long pointId : ids.get(DBNames.POINT)) {
			if (first) {
				first = false;
			} else {
				pointIds.append(",");
			}
			pointIds.append(pointId);
		}

		StringBuilder wayIds = new StringBuilder();
		first = true;
		for (Long wayId : ids.get(DBNames.WAY)) {
			if (first) {
				first = false;
			} else {
				wayIds.append(",");
			}
			wayIds.append(wayId);
		}

		StringBuilder poiIds = new StringBuilder();
		first = true;
		for (Long poiId : ids.get(DBNames.POI)) {
			if (first) {
				first = false;
			} else {
				poiIds.append(",");
			}
			poiIds.append(poiId);
		}

		if (ids.get(DBNames.WAY).size() > 0) {
			StringBuilder waySql = new StringBuilder();
			waySql.append("delete from way where way.id in (");
			waySql.append(wayIds).append(")");

			writableDataBase.execSQL(waySql.toString());
		}

		try {
			writableDataBase.beginTransaction();

			if (ids.get(DBNames.POINT).size() > 0) {
				StringBuilder pointSql = new StringBuilder();
				pointSql.append("delete from point where point.id in (");
				pointSql.append(pointIds).append(")");

				writableDataBase.execSQL(pointSql.toString());
			}

			if (ids.get(DBNames.POI).size() > 0) {
				StringBuilder poiSql = new StringBuilder();
				poiSql.append("delete from poi where poi.id in (");
				poiSql.append(poiIds).append(")");

				writableDataBase.execSQL(poiSql.toString());
			}

			String sqlPointsHaveWays = String.format(
					"delete from points_have_ways where points_have_ways.way_id in (%s)",
					wayIds.toString());
			writableDataBase.execSQL(sqlPointsHaveWays);

			String sqlPoiValue = String.format(
					"delete from poi_value where poi_value.poi_id in (%s)", poiIds.toString());
			writableDataBase.execSQL(sqlPoiValue);

			String sqlTrack = String.format("delete from track where track.id = %s",
					String.valueOf(trackId));
			writableDataBase.execSQL(sqlTrack);

			String sqlMedia = String.format("delete from media where media.point_id in (%s)",
					pointIds.toString());
			writableDataBase.execSQL(sqlMedia);

			String sqlWayValue = String.format(
					"delete from way_value where way_value.way_id in (%s)", wayIds);
			writableDataBase.execSQL(sqlWayValue);

			writableDataBase.setTransactionSuccessful();
			return true;
		} finally {
			writableDataBase.endTransaction();
		}
	}

	private Map<String, Collection<Long>> getPointWayPoiIdsFromTrack(long trackId) {
		Cursor cursor = null;
		try {

			Set<Long> ways = new TreeSet<Long>();
			Set<Long> points = new TreeSet<Long>();
			Set<Long> pois = new TreeSet<Long>();

			final String way = DBNames.WAY;
			final String poi = DBNames.POI;
			final String point = DBNames.POINT;

			String sql = String
					.format("select pId, wayId, poi.id as poiId from (select pId, phw.way_id as wayId from (select id as pId from point where point.track_id = %s) left outer join points_have_ways phw on (phw.point_id = pId)) left outer join poi on (poi.point_id = pId)",
							String.valueOf(trackId));

			cursor = readableDataBase.rawQuery(sql, null);

			int ipId = cursor.getColumnIndex("pId");
			int iwayId = cursor.getColumnIndex("wayId");
			int ipoiId = cursor.getColumnIndex("poiId");

			boolean first = true;

			while (cursor.moveToNext()) {

				if (first) {
					cursor.moveToFirst();
					first = false;
				}

				long pId = cursor.getLong(ipId);
				long wayId = cursor.getLong(iwayId);
				long poiId = cursor.getLong(ipoiId);

				if (pId != 0) {
					points.add(Long.valueOf(pId));
				}
				if (wayId != 0) {
					ways.add(Long.valueOf(wayId));
				}
				if (poiId != 0) {
					pois.add(Long.valueOf(poiId));
				}

			}
			cursor.close();
			Map<String, Collection<Long>> result = new TreeMap<String, Collection<Long>>();

			result.put(way, ways);
			result.put(poi, pois);
			result.put(point, points);

			return result;
		} finally {
			if (null != cursor) {
				cursor.close();
			}
		}
	}

	@Override
	public void setTrackName(long trackId, String newName) {

		String sql = String.format("update track set name = \"%s\" where id = %d;", newName,
				Long.valueOf(trackId));

		try {
			writableDataBase.beginTransaction();
			writableDataBase.execSQL(sql);
			writableDataBase.setTransactionSuccessful();
		} finally {
			writableDataBase.endTransaction();
		}

	}

	@Override
	public TrackData getTrackData(long trackId) {
		Cursor cursor = null;
		try {

			String sql = String
					.format("select pLng,pLat,points_have_ways.way_id "
							+ "as wayId from (select point.id as pId, point.lng as"
							+ " pLng, point.lat as pLat from point where point.track_id = %s)"
							+ " left outer join points_have_ways on (pId = points_have_ways.point_id);",
							String.valueOf(trackId));

			cursor = readableDataBase.rawQuery(sql, null);

			TrackData trackData = new TrackData(trackId, this);

			int ipLng = cursor.getColumnIndex("pLng");
			int ipLat = cursor.getColumnIndex("pLat");
			int iwayId = cursor.getColumnIndex("wayId");

			boolean first = true;

			while (cursor.moveToNext()) {

				if (first) {
					cursor.moveToFirst();
					first = false;
				}

				double lng = cursor.getDouble(ipLng);
				double lat = cursor.getDouble(ipLat);
				long wayId = cursor.getLong(iwayId);

				trackData.putPoint(lng, lat, wayId);
			}

			return trackData;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	@Override
	public boolean isArea(long wayId) {

		Cursor cursor = null;
		try {
			String sql = String.format("select way.is_area from way where id = %d",
					Long.valueOf(wayId));

			cursor = readableDataBase.rawQuery(sql, null);

			if (cursor.getCount() != 1) {
				cursor.close();
				throw new IllegalArgumentException(String.format("wayId %d was not found",
						Long.valueOf(wayId)));
			}
			cursor.moveToFirst();
			boolean isArea = cursor.getInt(0) == 1;

			return isArea;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	/**
	 * Get track data like id, name and date added in a record.
	 *
	 * @param trackId the id of the track you look for
	 * @return id, name and date added in a TrackRecord-structure or null, if
	 *         trackId does not exists in database
	 */

	@Override
	public TrackRecord getTrackById(long trackId) {

		TrackRecord result = null;
		Cursor cursor = null;
		try {
			// selection filters at most one record specified by the unique
			// track id
			final String whereClause = String.format("%s = \"%s\"", DBNames.Track.ID,
					String.valueOf(trackId));
			cursor = query(DBNames.TRACK, new String[]{DBNames.Track.ID, DBNames.Track.NAME,
					DBNames.Track.TIME}, whereClause, null);

			// read track data. The location is if any to find at first position
			if (cursor.moveToFirst()) { // let the cursor point to that
				// position, we
				// expect the data
				// and return the track informations in a record structure
				result = new TrackRecord(cursor.getLong(0), cursor.getString(1),
						cursor.getLong(2));
			}
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return result;
	}

	/**
	 * Get track data like id, name and date added in a record.
	 *
	 * @return id, name and date added in a TrackRecord-structure, or null, if
	 *         trackId does not exists in database
	 */
	@Override
	public String[] getAllTrackNames() {
		Cursor cursor = null;
		String[] result = null;
		try {

			readableDataBase.beginTransaction();

			cursor = query(DBNames.TRACK, new String[]{DBNames.Track.NAME}, null, null);

			// scale result array to store the result
			result = new String[cursor.getCount()];

			// traverse with cursor through db resultset
			if (cursor.moveToFirst()) {
				do {
					result[cursor.getPosition()] = cursor.getString(0);
				} while (cursor.moveToNext());
			}

			readableDataBase.setTransactionSuccessful();

		} finally {
			if (cursor != null)
				cursor.close();

			readableDataBase.endTransaction();
		}
		return result;

	}

	@Override
	public void close() {
		openHelper.close();
	}

	@Override
	public Collection<Way> getWaysByTrack(long trackId) {
		Cursor c = null;
		try {

			Collection<Way> ways = new ArrayList<Way>();
			String sql = String
					.format("select wayId, isArea, way_value.v as v, way_value.k as k from way_value  join (select wayId, way.is_area as isArea from (select distinct points_have_ways.way_id as wayId from points_have_ways where points_have_ways.point_id in (select point.id as pId from point where point.track_id = %d)) left join way on (way.id = wayId)) on (wayId = way_value.way_id);",
							trackId);
			c = readableDataBase.rawQuery(sql, null);

			int iwayId = c.getColumnIndex("wayId");
			int ik = c.getColumnIndex("k");
			int iv = c.getColumnIndex("v");
			int iisArea = c.getColumnIndex("isArea");

			assert iwayId != -1 && ik != -1 && iv != -1 && iisArea != -1;

			boolean first = true;
			long lastWayId = -1;
			Way way = null;

			while (c.moveToNext()) {
				if (first) {
					c.moveToFirst();
					first = false;
				}

				long wayId = c.getLong(iwayId);
				String k = c.getString(ik);
				String v = c.getString(iv);
				boolean isArea = c.getInt(iisArea) == 1;

				Log.d(TAG,
						String.format("found wayId: %d, key: %s, value: %s, isArea: %s",
								Long.valueOf(wayId), k, v, isArea ? "yes" : "no"));

				if (wayId != lastWayId) {
					lastWayId = wayId;
					if (way != null) {
						ways.add(way);
					}
					way = new Way(wayId, trackId, isArea);
				}

				if (k != null && way != null) {
					way.putTag(k, v);
				}
			}

			if (way != null) {
				ways.add(way);
			}

			return ways;
		} finally {
			if (c != null)
				c.close();
		}
	}

	@Override
	public String getNameFromTrack(long trackId) {
		Cursor c = null;
		try {

			String sql = String.format("select track.name from track where track.id = %d",
					Long.valueOf(trackId));
			c = readableDataBase.rawQuery(sql, null);
			String name;
			if (c.getCount() > 0) {
				c.moveToFirst();
				name = c.getString(0);
			} else {
				name = null;
			}
			return name;
		} finally {
			if (c != null)
				c.close();
		}
	}

	@Override
	public boolean setPoiValues(long poiId, Bundle values) {
		try {
			String deleteSql = String.format(
					"delete from poi_value where poi_value.poi_id = %d", poiId);
			Collection<String> insertSqls = new ArrayList<String>();

			for (String key : values.keySet()) {
				String value = values.getString(key);
				String insertSql = String.format(
						"INSERT INTO poi_value (poi_id,k,v) VALUES(%d,\"%s\",\"%s\")", poiId,
						key, value);
				insertSqls.add(insertSql);
			}

			writableDataBase.beginTransaction();

			writableDataBase.execSQL(deleteSql);

			for (String insertSql : insertSqls) {
				writableDataBase.execSQL(insertSql);
			}

			writableDataBase.setTransactionSuccessful();

			return true;

		} catch (SQLException e) {
			Log.e(TAG, "exception when setting new PoiValue to poiId: " + poiId, e);
			e.printStackTrace();
		} finally {
			writableDataBase.endTransaction();
		}
		return false;
	}

	@Override
	public boolean setWayValues(long wayId, Bundle values) {

		try {

			String deleteSql = String.format("delete from way_value where way_id = %d", wayId);
			Collection<String> insertSqls = new ArrayList<String>();

			for (String key : values.keySet()) {
				String value = values.getString(key);
				String insertSql = String.format(
						"INSERT INTO way_value (way_id,k,v) VALUES(%d,\"%s\",\"%s\")", wayId, key,
						value);
				insertSqls.add(insertSql);
			}

			writableDataBase.beginTransaction();

			writableDataBase.execSQL(deleteSql);

			for (String insertSql : insertSqls) {
				writableDataBase.execSQL(insertSql);
			}

			writableDataBase.setTransactionSuccessful();
			return true;
		} catch (SQLException e) {
			Log.e(TAG, "Exception setting way values for way: " + wayId, e);
			e.printStackTrace();
			return false;

		} finally {
			writableDataBase.endTransaction();
		}

	}

	@Override
	public boolean removeWay(long wayId) {
		try {
			writableDataBase.beginTransaction();

			String removeWaySql = String.format("delete from way where way.id = %d", wayId);
			String removePointWaySql = String.format("delete from points_have_ways where way_id = %d", wayId);
			String removeWayValuesSql = String.format(" delete from way_value where way_id = %d", wayId);

			writableDataBase.execSQL(removeWaySql);
			writableDataBase.execSQL(removePointWaySql);
			writableDataBase.execSQL(removeWayValuesSql);

			writableDataBase.setTransactionSuccessful();
			return true;
		} catch (Exception e) {
			Log.e(TAG, "error removing way: " + wayId, e);
			e.printStackTrace();
			return false;
		} finally {
			writableDataBase.endTransaction();
		}
	}

	@Override
	public void reopen() {
		if (!readableDataBase.isOpen()) {
			readableDataBase = openHelper.getReadableDatabase();
		}
		if (!writableDataBase.isOpen()) {
			writableDataBase = openHelper.getWritableDatabase();
		}

	}
}
